﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;

namespace EnheimGallery.DAL
{
    public class UserStatisticsData
    {
        public string Username;
        public int CategoriesCount;
        public int PhotosCount;
        public DateTime LastActivityDate;
        public string Description;
    }
    
    public static class UsersStatisticsDAL
    {
        
        public static List<UserStatisticsData> UsersStatistics()
        {
            EnheimGalleryDatabaseEntities db = new EnheimGalleryDatabaseEntities();
            string nativeSqlQuery = @"/*users with photos*/ 
                            select u.UserName as username,count(distinct(c.Id)) as catcount,count(p.Id) as imagecount,u.LastActivityDate as lastActivityDate,
                            a.Description as description
                            from aspnet_Users u
                            join UserActions a on a.ActionId=u.LastActionId
                            join Categories c on u.UserId=c.OwnerId
                            left outer join Photos p on p.CategoryId=c.Id 
                            group by u.UserName,u.LastActivityDate,a.Description

                            union
                            /*users without photos*/

                            select u.UserName as username,count(distinct(c.Id)) as catcount,0 as imagecount,u.LastActivityDate as lastActivityDate,
                            a.Description as description
                            from aspnet_Users u
                            join UserActions a on a.ActionId=u.LastActionId
                            join Categories c on u.UserId=c.OwnerId
                            where not exists(select 1 from Categories c
                                             join Photos p
                                             on p.CategoryId=c.Id
                                             where c.OwnerId=u.UserId
                                             )
                            group by u.UserName,u.LastActivityDate,a.Description";
           // return salesQuery;

            ObjectResult<UserStatisticsData> queryResult = db.ExecuteStoreQuery<UserStatisticsData>(nativeSqlQuery);
            List<UserStatisticsData> users = queryResult.ToList();
            return users;
            //   List<UserStatistics> us= db.CreateQuery<UserStatistics>(nativeSqlQuery).ToList();

        //   List<UserStatistics> returnusers = users.ToList();
         //  return returnusers;
       }
        public static List<string> GetUserNames()
        {
           EnheimGalleryDatabaseEntities db = new EnheimGalleryDatabaseEntities();
           var userNames = from user in db.aspnet_Users
                           select user.UserName;

           List<string> returnList = userNames.ToList();

           return returnList;
        }
        public static aspnet_Users FindUser(string username)
        {
            EnheimGalleryDatabaseEntities db = new EnheimGalleryDatabaseEntities();
            var resultUser = from user in db.aspnet_Users
                       where user.UserName == username
                       select user;
            return resultUser.First();
        }

    }
}
